package impl;

import bean.GoodInfo;
import dao.GoodInfoDao;
import util.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @classname:GoodInfoDaoImpl
 * @description:
 * @author: Mr.Z
 * @date: 2021/1/1 11:46
 * @version: 0.1
 * @since:1.8
 */
public class GoodInfoDaoImpl implements GoodInfoDao {
    private Connection conn;
    private String sql;
    private PreparedStatement ps;
    private ResultSet rs;
    private int result;

    @Override
    public int addGood(GoodInfo goodInfo) {
        conn = DBHelper.getCon();
        sql = "insert into goodinfo (gname,pid,number,price,gstatus,discount) values (?,?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, goodInfo.getGname());
            ps.setInt(2, goodInfo.getPid());
            ps.setInt(3, goodInfo.getNumber());
            ps.setFloat(4, goodInfo.getPrice());
            ps.setInt(5, goodInfo.getGstatus());
            ps.setFloat(6, goodInfo.getDiscount());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }

    @Override
    public GoodInfo selectGoodByPid(int pid) {
        conn = DBHelper.getCon();
        sql = "select count(*) from goodinfo where pid=?";
        GoodInfo goodInfo = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, pid);
            rs = ps.executeQuery();

            if (rs.next()) {
                goodInfo = new GoodInfo(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }
        return goodInfo;
    }

    public GoodInfo selectGoodById(int id) {
        conn = DBHelper.getCon();
        sql = "select * from goodinfo where id=?";
        GoodInfo goodInfo = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                goodInfo = new GoodInfo(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }
        return goodInfo;
    }


    @Override
    public List<Map<String, Object>> rankByType() {
        conn = DBHelper.getCon();
        sql = "select \n" +
                "p.typeName,g.gname,sum(d.count) sum\n" +
                "from\n" +
                "goodinfo g,product p,detail d,orderinfo o\n" +
                "where month(o.datetime)=1 and g.pid=p.pid and g.id=d.id and d.uid=o.uid\n" +
                "GROUP BY d.id\n" +
                "ORDER BY sum desc LIMIT 10;\n";
        List<Map<String, Object>> list = new ArrayList<>(10);
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new LinkedHashMap<>(16);
                map.put("typeName", rs.getObject("typeName"));
                map.put("age", rs.getObject("gname"));
                map.put("sum", rs.getObject("sum"));
                list.add(map);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.closeResources(conn,ps,rs);
        }
        return list;
    }


    @Override
    public int deleteGoodById(int id) {
        conn = DBHelper.getCon();
        sql = "delete from goodinfo where id = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }

        return result;
    }

    @Override
    public List<GoodInfo> selectAll() {
        conn = DBHelper.getCon();
        sql = "select * from goodinfo";
        List<GoodInfo> list = new ArrayList<>(10);
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new GoodInfo(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }
        return list;
    }

    private int count = 0;


    @Override
    public List<GoodInfo> selectGoodByParams(Map<String, Object> params) {
        conn = DBHelper.getCon();
        StringBuilder builder = new StringBuilder("select * from goodinfo where ");
        List<GoodInfo> goodInfos = new ArrayList<>(10);

        params.forEach((k, v) -> {
            count++;
            builder.append(k);
            builder.append("=");
            builder.append("'");
            builder.append(v);
            builder.append("'");
            if (count < params.size()) {
                builder.append(" and ");
            }
        });
        //System.out.println(builder);
        try {
            ps = conn.prepareStatement(builder.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                goodInfos.add(new GoodInfo(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps, rs);
        }
        return goodInfos;
    }

    @Override
    public int updateGoodInfoById(GoodInfo goodInfo, int id) {
        conn = DBHelper.getCon();
        sql = "update goodinfo set gname=?,pid=?,number=?,price=?,gstatus=?,discount=? where id =?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, goodInfo.getGname());
            ps.setInt(2, goodInfo.getPid());
            ps.setInt(3, goodInfo.getNumber());
            ps.setFloat(4, goodInfo.getPrice());
            ps.setInt(5, goodInfo.getGstatus());
            ps.setFloat(6, goodInfo.getDiscount());
            ps.setInt(7, goodInfo.getId());
            result = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }
}
